﻿using System;
using System.Collections;
using System.Collections.Generic;
using NHibernate;
using ParaEditor.Common.NHibernate;
using ParaEditor.DomainSource.Entities;

namespace ParaEditor.DAO
{
    public class SourceBaseDAO
    {
        protected static ISession Session { get; set; }
        private NHibernateMember instance = NHibernateMember.GetInstanceSource();
        private object obj;

        public SourceBaseDAO(object _obj)
        {
            Session = instance.GetSessionSource();
            obj = _obj;
        }

        public SourceBaseDAO()
        {
            Session = instance.GetSessionSource();
        }

        public IList<CrLineInfo> GetCurrentLineInfo()
        {
            string sqlText = "select * from tbl_cr_line_info where alg_ver = (select alg_ver from TBL_CR_ALG_INFO where current_alg = 1)";
            ISQLQuery query = Session.CreateSQLQuery(sqlText).AddEntity("ParaEditor.DomainSource.Entities.CrLineInfo");
            IList<CrLineInfo> list = query.List<CrLineInfo>();
            return list;
        }

        public IList<CrStationInfo> GetCurrentStationInfo()
        {
            try
            {
                string sqlText = "select * from tbl_cr_station_info where alg_ver = (select alg_ver from TBL_CR_ALG_INFO where current_alg = 1)";
                ISQLQuery query = Session.CreateSQLQuery(sqlText).AddEntity("ParaEditor.DomainSource.Entities.CrStationInfo");
                IList<CrStationInfo> list = query.List<CrStationInfo>();
                return list;
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public IList<CalcArea> GetCurrentCalcArea()
        {
            string sqlText = "";
            sqlText += " select a.alg_ver,";
            sqlText += "        lpad(b.line_id, 2, '0') || lpad(b.station_id, 2, '0') o_id,";
            sqlText += "        lpad(c.line_id, 2, '0') || lpad(c.station_id, 2, '0') d_id,";
            sqlText += "        path_length,";
            sqlText += "        area_code";
            sqlText += "   from (select alg_ver, o_id, d_id, path_length, area_code";
            sqlText += "           from TBL_CR_CALC_AREA";
            sqlText += "          where alg_ver =";
            sqlText += "                (select alg_ver from TBL_CR_ALG_INFO where current_alg = 1)) a";
            sqlText += "   left join (select alg_ver, line_id, station_id, station_index";
            sqlText += "                from tbl_cr_station_info";
            sqlText += "               where alg_ver = (select alg_ver";
            sqlText += "                                  from TBL_CR_ALG_INFO";
            sqlText += "                                 where current_alg = 1)) b";
            sqlText += "     on (b.station_index = a.o_id)";
            sqlText += "   left join (select alg_ver, line_id, station_id, station_index";
            sqlText += "                from tbl_cr_station_info";
            sqlText += "               where alg_ver = (select alg_ver";
            sqlText += "                                  from TBL_CR_ALG_INFO";
            sqlText += "                                 where current_alg = 1)) c";
            sqlText += "     on (c.station_index = a.d_id)";

            ISQLQuery query = Session.CreateSQLQuery(sqlText).AddEntity("ParaEditor.DomainSource.Entities.CalcArea");
            IList<CalcArea> list = query.List<CalcArea>();
            return list;
        }

        public IList<IncomeProportion> GetCurrentIncomeProportion()
        {
            string sqlText = "";
            
            sqlText += " select alg_ver,    ";
            sqlText += "        b.line_id || lpad(b.station_id, 2, '0') o_id,";
            sqlText += "        c.line_id || lpad(c.station_id, 2, '0') d_id,";
            sqlText += "        '0' line_id,";
            sqlText += "        owner_id,";
            sqlText += "        income_proportion";
            sqlText += "   from TBL_CR_INCOME_PROPORTION a";
            sqlText += "   left join (select line_id, station_id, station_index";
            sqlText += "                from TBL_CR_STATION_INFO where alg_ver = (select alg_ver from TBL_CR_ALG_INFO where current_alg = 1)) b";
            sqlText += "     on (a.o_id = b.station_index)";
            sqlText += "   left join (select line_id, station_id, station_index";
            sqlText += "                from TBL_CR_STATION_INFO  where alg_ver = (select alg_ver from TBL_CR_ALG_INFO where current_alg = 1)) c";
            sqlText += "     on (a.d_id = c.station_index)";
            sqlText += "  where alg_ver =";
            sqlText += "        (select alg_ver from TBL_CR_ALG_INFO where current_alg = 1)";
            sqlText += "  order by o_id, d_id asc";


            /*
            sqlText += " select aa.alg_ver     alg_ver,";
            sqlText += "        aa.o_id        o_id,";
            sqlText += "        aa.d_id        d_id,";
            sqlText += "        aa.owner_count owner_id,";
            sqlText += "        bb.price *100      line_id,";
            sqlText += "        0              INCOME_PROPORTION";
            sqlText += "   from (select alg_ver,";
            sqlText += "                lpad(b.line_id, 2, '0') || lpad(b.station_id, 2, '0') o_id,";
            sqlText += "                lpad(c.line_id, 2, '0') || lpad(c.station_id, 2, '0') d_id,";
            sqlText += "                count(owner_id) owner_count";
            sqlText += "           from TBL_CR_INCOME_PROPORTION a";
            sqlText += "           left join (select line_id, station_id, station_index";
            sqlText += "                       from TBL_CR_STATION_INFO  where alg_ver = (select alg_ver from TBL_CR_ALG_INFO where current_alg = 1)) b";
            sqlText += "             on (a.o_id = b.station_index)";
            sqlText += "           left join (select line_id, station_id, station_index";
            sqlText += "                       from TBL_CR_STATION_INFO  where alg_ver = (select alg_ver from TBL_CR_ALG_INFO where current_alg = 1)) c";
            sqlText += "             on (a.d_id = c.station_index)";
            sqlText += "          where alg_ver =";
            sqlText += "                (select alg_ver from TBL_CR_ALG_INFO where current_alg = 1)";
            sqlText += "          group by alg_ver,";
            sqlText += "                   lpad(b.line_id, 2, '0') || lpad(b.station_id, 2, '0'),";
            sqlText += "                   lpad(c.line_id, 2, '0') || lpad(c.station_id, 2, '0')";
            sqlText += "          order by o_id, d_id asc) aa";
            sqlText += "   left join (select alg_ver,";
            sqlText += "                     lpad(b.line_id, 2, '0') || lpad(b.station_id, 2, '0') o_id,";
            sqlText += "                     lpad(d.line_id, 2, '0') || lpad(d.station_id, 2, '0') d_id,";
            sqlText += "                     area_code + 2 price";
            sqlText += "                from TBL_CR_CALC_AREA a";
            sqlText += "                left join (select line_id, station_id, station_index";
            sqlText += "                            from TBL_CR_STATION_INFO  where alg_ver = (select alg_ver from TBL_CR_ALG_INFO where current_alg = 1)) b";
            sqlText += "                  on (a.o_id = b.station_index)";
            sqlText += "                left join (select line_id, station_id, station_index";
            sqlText += "                            from TBL_CR_STATION_INFO  where alg_ver = (select alg_ver from TBL_CR_ALG_INFO where current_alg = 1)) d";
            sqlText += "                  on (a.d_id = d.station_index)) bb";
            sqlText += "     on (aa.alg_ver = bb.alg_ver and aa.o_id = bb.o_id and aa.d_id = bb.d_id)";
            */
            ISQLQuery query = Session.CreateSQLQuery(sqlText).AddEntity("ParaEditor.DomainSource.Entities.IncomeProportion");
            IList<IncomeProportion> list = query.List<IncomeProportion>();
            return list;
        }

        public IList<OwnerInfo> GetCurrentOwnerInfo()
        {
            string sqlText = "select * from TBL_CR_OWNER_INFO where alg_ver = (select alg_ver from TBL_CR_ALG_INFO where current_alg = 1)";
            ISQLQuery query = Session.CreateSQLQuery(sqlText).AddEntity("ParaEditor.DomainSource.Entities.OwnerInfo");
            IList<OwnerInfo> list = query.List<OwnerInfo>();
            return list;
        }

        public IList<SubRoad> GetCurrenSubRoad()
        {
            string sqlText = "";
            sqlText += " select alg_ver,";
            sqlText += "        road_seq,";
            sqlText += "        o_id,";
            sqlText += "        d_id,";
            sqlText += "        way_id,";
            sqlText += "        subroad_seq,";
            sqlText += "        way_percent,";
            sqlText += "        sub_orig_index,";
            sqlText += "        bbbb.staid sub_dest_index,";
            sqlText += "        subroad_length,";
            sqlText += "        subroad_time";
            sqlText += "   from (select alg_ver,";
            sqlText += "                road_seq,";
            sqlText += "                o_id,";
            sqlText += "                d_id,";
            sqlText += "                way_id,";
            sqlText += "                subroad_seq,";
            sqlText += "                way_percent,";
            sqlText += "                bbb.staid sub_orig_index,";
            sqlText += "                sub_dest_index,";
            sqlText += "                subroad_length,";
            sqlText += "                subroad_time";
            sqlText += "           from (select alg_ver,";
            sqlText += "                        road_seq,";
            sqlText += "                        o_id,";
            sqlText += "                        bb.staid d_id,";
            sqlText += "                        way_id,";
            sqlText += "                        subroad_seq,";
            sqlText += "                        way_percent,";
            sqlText += "                        sub_orig_index,";
            sqlText += "                        sub_dest_index,";
            sqlText += "                        subroad_length,";
            sqlText += "                        subroad_time";
            sqlText += "                   from (select alg_ver,";
            sqlText += "                                road_seq,";
            sqlText += "                                b.staid o_id,";
            sqlText += "                                d_id,";
            sqlText += "                                way_id,";
            sqlText += "                                subroad_seq,";
            sqlText += "                                way_percent,";
            sqlText += "                                sub_orig_index,";
            sqlText += "                                sub_dest_index,";
            sqlText += "                                subroad_length,";
            sqlText += "                                subroad_time";
            sqlText += "                           from tbl_cr_sub_road a";
            sqlText += "                           left join (select sta.station_index staindex,";
            sqlText += "                                            lpad(sta.line_id, 2, '0') ||";
            sqlText += "                                            sta.station_id staid";
            sqlText += "                                       from tbl_cr_station_info sta";
            sqlText += "                                      order by station_index) b";
            sqlText += "                             on (a.o_id = b.staindex)) aa";
            sqlText += "                   left join (select sta.station_index staindex,";
            sqlText += "                                    lpad(sta.line_id, 2, '0') ||";
            sqlText += "                                    sta.station_id staid";
            sqlText += "                               from tbl_cr_station_info sta";
            sqlText += "                              order by station_index) bb";
            sqlText += "                     on (aa.d_id = bb.staindex)) aaa";
            sqlText += "           left join (select sta.station_index staindex,";
            sqlText += "                            lpad(sta.line_id, 2, '0') || sta.station_id staid";
            sqlText += "                       from tbl_cr_station_info sta";
            sqlText += "                      order by station_index) bbb";
            sqlText += "             on (aaa.sub_orig_index = bbb.staindex)) aaaa";
            sqlText += "   left join (select sta.station_index staindex,";
            sqlText += "                     lpad(sta.line_id, 2, '0') || sta.station_id staid";
            sqlText += "                from tbl_cr_station_info sta";
            sqlText += "               order by station_index) bbbb";
            sqlText += "     on (aaaa.sub_dest_index = bbbb.staindex)";

            ISQLQuery query = Session.CreateSQLQuery(sqlText).AddEntity("ParaEditor.DomainSource.Entities.SubRoad");
            IList<SubRoad> list = query.List<SubRoad>();
            return list;
        }

        private int GetALgVer()
        {
            string sqlText = "select alg_ver from TBL_CR_ALG_INFO where current_alg = 1";
            ISQLQuery query = Session.CreateSQLQuery(sqlText).AddScalar("alg_ver", NHibernateUtil.Int32);
            int maxid = Convert.ToInt32(query.UniqueResult());
            return maxid;
        }

        public Hashtable InitSubRoadCountHashTable()
        {
            Hashtable tableCount = new Hashtable();
            string sqlText = "";
            sqlText += " select count(distinct subroad_seq) subroad_count,";
            sqlText += "        road_seq || o_id || d_id || way_id key";
            sqlText += "   from (select alg_ver,";
            sqlText += "                road_seq,";
            sqlText += "                nvl(lpad(b.line_id, 2, '0') ||";
            sqlText += "                              lpad(b.station_id, 2, '0'),";
            sqlText += "                              '0000') o_id,";
            sqlText += "                nvl(lpad(d.line_id, 2, '0') ||";
            sqlText += "                              lpad(d.station_id, 2, '0'),";
            sqlText += "                              '0000') d_id,";
            sqlText += "                way_id,";
            sqlText += "                subroad_seq,";
            sqlText += "                way_percent,";
            sqlText += "                to_number(nvl(lpad(e.line_id, 2, '0') ||";
            sqlText += "                              lpad(e.station_id, 2, '0'),";
            sqlText += "                              '0000')) sub_orig_index,";
            sqlText += "                to_number(nvl(lpad(f.line_id, 2, '0') ||";
            sqlText += "                              lpad(f.station_id, 2, '0'),";
            sqlText += "                              '0000')) sub_dest_index,";
            sqlText += "                subroad_length,";
            sqlText += "                subroad_time";
            sqlText += "           from TBL_CR_SUB_ROAD a";
            sqlText += "           left join (select line_id, station_id, station_index";
            sqlText += "                       from TBL_CR_STATION_INFO  where alg_ver = (select alg_ver from TBL_CR_ALG_INFO where current_alg = 1)) b";
            sqlText += "             on (a.o_id = b.station_index)";
            sqlText += "           left join (select line_id, station_id, station_index";
            sqlText += "                       from TBL_CR_STATION_INFO  where alg_ver = (select alg_ver from TBL_CR_ALG_INFO where current_alg = 1)) d";
            sqlText += "             on (a.d_id = d.station_index)";
            sqlText += "           left join (select line_id, station_id, station_index";
            sqlText += "                       from TBL_CR_STATION_INFO  where alg_ver = (select alg_ver from TBL_CR_ALG_INFO where current_alg = 1)) e";
            sqlText += "             on (a.sub_orig_index = d.station_index)";
            sqlText += "           left join (select line_id, station_id, station_index";
            sqlText += "                       from TBL_CR_STATION_INFO  where alg_ver = (select alg_ver from TBL_CR_ALG_INFO where current_alg = 1)) f";
            sqlText += "             on (a.sub_dest_index = d.station_index)";
            sqlText += "          where alg_ver =";
            sqlText += "                (select alg_ver from TBL_CR_ALG_INFO where current_alg = 1))";
            sqlText += "                group by road_seq,o_id,d_id,way_id";
            ISQLQuery query = Session.CreateSQLQuery(sqlText).AddScalar("subroad_count", NHibernateUtil.Int64).AddScalar("key", NHibernateUtil.String);
            IList list = query.List();

            foreach (object[] obj in list)
            {
                tableCount.Add(obj[1], obj[0]);
            }
            return tableCount;
        }


        public Hashtable InitPrice()
        {
            Hashtable TablePrice = new Hashtable();
            string sqlText = "";
            sqlText += " select lpad(b.line_id, 2, '0') || lpad(b.station_id, 2, '0') ||";
            sqlText += "        lpad(d.line_id, 2, '0') || lpad(d.station_id, 2, '0') key,";
            sqlText += "        (area_code + 2) * 100 price";
            sqlText += "   from TBL_CR_CALC_AREA a";
            sqlText += "   left join (select line_id, station_id, station_index";
            sqlText += "                from TBL_CR_STATION_INFO  where alg_ver = (select alg_ver from TBL_CR_ALG_INFO where current_alg = 1)) b";
            sqlText += "     on (a.o_id = b.station_index)";
            sqlText += "   left join (select line_id, station_id, station_index";
            sqlText += "                from TBL_CR_STATION_INFO  where alg_ver = (select alg_ver from TBL_CR_ALG_INFO where current_alg = 1)) d";
            sqlText += "     on (a.d_id = d.station_index)";
            sqlText += "  where alg_ver =";
            sqlText += "        (select alg_ver from tbl_cr_alg_info where current_alg = 1)";
            ISQLQuery query = Session.CreateSQLQuery(sqlText).AddScalar("price", NHibernateUtil.Int16).AddScalar("key", NHibernateUtil.String);
            IList list = query.List();

            foreach (object[] obj in list)
            {
                TablePrice.Add(obj[1], obj[0]);
            }
            return TablePrice;
        }

        /// <summary>
        /// 将起始站对应的走法数量生成哈希表
        /// </summary>
        /// <returns></returns>
        public Hashtable InitWayCountHashTable()
        {
            Hashtable TableCount = new Hashtable();
            string sqlText = "";
            sqlText += " select count(distinct way_id) way_count,road_seq||lpad(o_id,4,'0')||lpad(d_id,4,'0') key";
            sqlText += "   from (select alg_ver,";
            sqlText += "                road_seq,";
            sqlText += "                to_number(nvl(lpad(b.line_id, 2, '0') ||";
            sqlText += "                              lpad(b.station_id, 2, '0'),";
            sqlText += "                              '0000')) o_id,";
            sqlText += "                to_number(nvl(lpad(d.line_id, 2, '0') ||";
            sqlText += "                              lpad(d.station_id, 2, '0'),";
            sqlText += "                              '0000')) d_id,";
            sqlText += "                way_id,";
            sqlText += "                subroad_seq,";
            sqlText += "                way_percent,";
            sqlText += "                to_number(nvl(lpad(e.line_id, 2, '0') ||";
            sqlText += "                              lpad(e.station_id, 2, '0'),";
            sqlText += "                              '0000')) sub_orig_index,";
            sqlText += "                to_number(nvl(lpad(f.line_id, 2, '0') ||";
            sqlText += "                              lpad(f.station_id, 2, '0'),";
            sqlText += "                              '0000')) sub_dest_index,";
            sqlText += "                subroad_length,";
            sqlText += "                subroad_time";
            sqlText += "           from TBL_CR_SUB_ROAD a";
            sqlText += "           left join (select line_id, station_id, station_index";
            sqlText += "                       from TBL_CR_STATION_INFO  where alg_ver = (select alg_ver from TBL_CR_ALG_INFO where current_alg = 1)) b";
            sqlText += "             on (a.o_id = b.station_index)";
            sqlText += "           left join (select line_id, station_id, station_index";
            sqlText += "                       from TBL_CR_STATION_INFO  where alg_ver = (select alg_ver from TBL_CR_ALG_INFO where current_alg = 1)) d";
            sqlText += "             on (a.d_id = d.station_index)";
            sqlText += "           left join (select line_id, station_id, station_index";
            sqlText += "                       from TBL_CR_STATION_INFO  where alg_ver = (select alg_ver from TBL_CR_ALG_INFO where current_alg = 1)) e";
            sqlText += "             on (a.sub_orig_index = d.station_index)";
            sqlText += "           left join (select line_id, station_id, station_index";
            sqlText += "                       from TBL_CR_STATION_INFO  where alg_ver = (select alg_ver from TBL_CR_ALG_INFO where current_alg = 1)) f";
            sqlText += "             on (a.sub_dest_index = d.station_index)";
            sqlText += "          where alg_ver =";
            sqlText += "                (select alg_ver from TBL_CR_ALG_INFO where current_alg = 1))";
            sqlText += "                group by road_seq, o_id, d_id";
            ISQLQuery query = Session.CreateSQLQuery(sqlText).AddScalar("way_count", NHibernateUtil.Int32).AddScalar("key", NHibernateUtil.String);
            IList list = query.List();

            foreach (object[] obj in list)
            {
                TableCount.Add(obj[1], obj[0]);
            }
            return TableCount;
        }

        /// <summary>
        /// 根据进站id 出站id查询其子路段上的换乘站信息
        /// </summary>
        /// <returns></returns>
        public List<TranStation> GetTranStaion()
        {
            List<TranStation> listResult = new List<TranStation>();
            string sqlText = "";
            sqlText += " select lpad(e.line_id, 2, '0') || lpad(e.station_id, 2, '0') ||";
            sqlText += "        lpad(f.line_id, 2, '0') || lpad(f.station_id, 2, '0') key,";
            sqlText += "        tran";
            sqlText += "   from (select o_id,";
            sqlText += "                d_id,";
            sqlText += "                lpad(h.line_id, 2, '0') || lpad(h.station_id, 2, '0') tran";
            sqlText += "           from (select o_id, d_id, tran";
            sqlText += "                   from (select a.o_id, d_id, b.o_id tran";
            sqlText += "                           from TBL_CR_SUB_ROAD a";
            sqlText += "                           left join (select o_id";
            sqlText += "                                       from TBL_CR_STATION_TRANSFER";
            sqlText += "                                      where alg_ver =";
            sqlText += "                                            (select alg_ver";
            sqlText += "                                               from tbl_cr_alg_info";
            sqlText += "                                              where current_alg = 1)) b";
            sqlText += "                             on (b.o_id = a.sub_orig_index)) c";
            sqlText += "                  where tran is not null) i";
            sqlText += "           left join (select station_id, line_id, station_index";
            sqlText += "                       from tbl_cr_station_info  where alg_ver = (select alg_ver from TBL_CR_ALG_INFO where current_alg = 1)) h";
            sqlText += "             on (h.station_index = i.tran)) d";
            sqlText += "   left join (select line_id, station_id, station_index";
            sqlText += "                from TBL_CR_STATION_INFO  where alg_ver = (select alg_ver from TBL_CR_ALG_INFO where current_alg = 1)) e";
            sqlText += "     on (e.station_index = d.o_id)";
            sqlText += "   left join (select line_id, station_id, station_index";
            sqlText += "                from TBL_CR_STATION_INFO  where alg_ver = (select alg_ver from TBL_CR_ALG_INFO where current_alg = 1)) f";
            sqlText += "     on (f.station_index = d.d_id)";
            sqlText += "  order by key asc";
            ISQLQuery query = Session.CreateSQLQuery(sqlText).AddScalar("key", NHibernateUtil.String).AddScalar("tran", NHibernateUtil.String);
            IList list = query.List();
            TranStation obj = null;
            foreach (object[] objlist in list)
            {
                obj = new TranStation();
                obj.key = objlist[0].ToString();
                obj.value = objlist[1].ToString();
                listResult.Add(obj);
            }
            return listResult;
        }
    }
}
